﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

using MINERP.MODEL.Monitor;

namespace MINERP.DAL.Monitor
{
    public class MonitorDAL
    {
        //string conStr = ConfigurationManager.ConnectionStrings["fffff"].ToString();
        string conStr = DALBase.ConnStr;
        SqlConnection con;
        List<UserTaskInfo> userTaskInfoList = new List<UserTaskInfo>();
        /// <summary>
        /// 监控的总体信息
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="priority"></param>
        /// <returns></returns>
        public List<MODEL.Monitor.MonitorInfo> GetMonitorInfo(DateTime start,DateTime end,int priority)
        {
            List<MODEL.Monitor.MonitorInfo> monitors = new List<MINERP.MODEL.Monitor.MonitorInfo>();
;
            using (con = new SqlConnection(conStr))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandTimeout = 600;
                    SqlDataReader reader;
                    //客服 
                    MODEL.Monitor.MonitorInfo kefuM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string kefuSql1 = "SELECT ROUND(SUM((a.Height * 2.54 / 100) * (a.Width * 2.54 / 100)*b.productNum), 4) AS CompletedArea, COUNT(DISTINCT b.OrderProductId) AS CompletedTask FROM (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as a ";
                    kefuSql1 += " inner join (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId INNER JOIN (select * from tb_orderbag union all select * from tb_orderbag_bk) as c ON b.OrderBagId = c.OrderBagId";
                    kefuSql1 += " WHERE (c.OrderTime >= '" + start + "') AND (c.OrderTime < '" + end + "')";
                    //string kefuSql1 = "SELECT ROUND(SUM((a.Height * 2.54 / 100) * (a.Width * 2.54 / 100)*b.productNum), 4) AS CompletedArea, COUNT(DISTINCT b.OrderProductId) AS CompletedTask FROM (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as a ";
                    //kefuSql1 += " inner join (select * from orderinfo where orderinfo.OrderTime >= '" + start + "' and  orderinfo.OrderTime <= '" + end + "' and (orderinfo.status==1 or orderinfo.status==8)  union all select * from orderinfo_bk orderinfo_bk.OrderTime >= '" + start + "' and  orderinfo_bk.OrderTime <= '" + end + "' and (orderinfo_bk.status==1 or orderinfo_bk.status==8)) as b ON a.OrderProductId = b.OrderProductId INNER JOIN (select * from tb_orderbag union all select * from tb_orderbag_bk) as c ON b.OrderBagId = c.OrderBagId";
                    //kefuSql1 += " WHERE (c.OrderTime >= '" + start + "') AND (c.OrderTime < '" + end + "')";
                    cmd.CommandText = kefuSql1;
                    con.Open();
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            kefuM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            kefuM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //加急
                    string kefuSql2 = "SELECT ROUND(SUM((a.Height * 2.54 / 100) * (a.Width * 2.54 / 100)*b.productNum), 4) AS PriorityArea, COUNT(DISTINCT b.OrderProductId) AS PriorityTask FROM (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as a";
                    kefuSql2 += " inner join (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId INNER JOIN (select * from tb_orderbag union all select * from tb_orderbag_bk) as c ON b.OrderBagId = c.OrderBagId";
                    kefuSql2 += " WHERE (c.OrderTime >= '" + start + "') AND (c.OrderTime < '" + end + "') and b.priority>=" + priority;
                    //string kefuSql2 = "SELECT ROUND(SUM((a.Height * 2.54 / 100) * (a.Width * 2.54 / 100)*b.productNum), 4) AS CompletedArea, COUNT(DISTINCT b.OrderProductId) AS CompletedTask FROM (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as a ";
                    //kefuSql1 += " inner join (select * from orderinfo where orderinfo.OrderTime >= '" + start + "' and  orderinfo.OrderTime <= '" + end + "' and (orderinfo.status==1 ororderinfo.status==8) and orderinfo.priority>='" + priority + "' union all select * from orderinfo_bk orderinfo_bk.OrderTime >= '" + start + "' and  orderinfo_bk.OrderTime <= '" + end + "' and (orderinfo_bk.status==1 orderinfo_bk.status==8) and orderinfo_bk.priority>='" + priority + "') as b ON a.OrderProductId = b.OrderProductId INNER JOIN (select * from tb_orderbag union all select * from tb_orderbag_bk) as c ON b.OrderBagId = c.OrderBagId";
                    kefuSql1 += " WHERE (c.OrderTime >= '" + start + "') AND (c.OrderTime < '" + end + "')";
                    cmd.CommandText = kefuSql2;

                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityTask"] != DBNull.Value)
                            kefuM.PriorityTaskNoComplete = Convert.ToInt32(reader["PriorityTask"]);
                        if (reader["PriorityArea"] != DBNull.Value)
                            kefuM.PriorityAreaNoComplete = Convert.ToInt32(reader["PriorityArea"]);
                    }
                    reader.Close();
                    kefuM.TechId = 1;
                    kefuM.TechName = "客服";
                    monitors.Add(kefuM);

                    //调色 1未完成
                    MODEL.Monitor.MonitorInfo tiaoseM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string tiaoseSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    tiaoseSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo  as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4  and b.status=1 INNER JOIN";
                    tiaoseSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 4)";
                    cmd.CommandText = tiaoseSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            tiaoseM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            tiaoseM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string tiaoseSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT( b.OrderProductId) AS CompletedTask";
                    tiaoseSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) as a INNER JOIN (select * from orderInfo union all select * from orderInfo_bk) as b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    tiaoseSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoInfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoInfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 4) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = tiaoseSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            tiaoseM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            tiaoseM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string tiaoseSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    tiaoseSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    tiaoseSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 4)";
                    cmd.CommandText = tiaoseSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            tiaoseM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            tiaoseM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    tiaoseM.TechId = 4;
                    tiaoseM.TechName = "调色";
                    monitors.Add(tiaoseM);

                    //分配打印机
                    MODEL.Monitor.MonitorInfo dayinjiM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string dayinjiSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    dayinjiSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4 and b.status=1 INNER JOIN";
                    dayinjiSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 5)";
                    cmd.CommandText = dayinjiSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            dayinjiM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            dayinjiM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string dayinjiSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT(DISTINCT b.OrderProductId) AS CompletedTask";
                    dayinjiSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) as a INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    dayinjiSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 5) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = dayinjiSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            dayinjiM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            dayinjiM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string dayinjiSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    dayinjiSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    dayinjiSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 5)";
                    cmd.CommandText = dayinjiSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            dayinjiM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            dayinjiM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    dayinjiM.TechId = 5;
                    dayinjiM.TechName = "打印管理";

                    //输出管理 1未完成
                    MODEL.Monitor.MonitorInfo shuchuM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string shuchuSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    shuchuSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4 INNER JOIN";
                    shuchuSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 7)";
                    cmd.CommandText = shuchuSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            shuchuM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            shuchuM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string shuchuSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT(DISTINCT b.OrderProductId) AS CompletedTask";
                    shuchuSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) as a INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    shuchuSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 7) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = shuchuSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            shuchuM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            shuchuM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string shuchuSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    shuchuSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    shuchuSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 7)";
                    cmd.CommandText = shuchuSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            shuchuM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            shuchuM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    shuchuM.TechId = 7;
                    shuchuM.TechName = "输出管理";
                    monitors.Add(dayinjiM);

                    //分片 1未完成
                    MODEL.Monitor.MonitorInfo fenpianM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string fenpianSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    fenpianSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4 INNER JOIN";
                    fenpianSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 8)";
                    cmd.CommandText = fenpianSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            fenpianM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            fenpianM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string fenpianSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT(DISTINCT b.OrderProductId) AS CompletedTask";
                    fenpianSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) as a INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    fenpianSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 8) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = fenpianSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            fenpianM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            fenpianM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string fenpianSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    fenpianSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    fenpianSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 8)";
                    cmd.CommandText = fenpianSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            fenpianM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            fenpianM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    fenpianM.TechId = 8;
                    fenpianM.TechName = "分片";
                    monitors.Add(fenpianM);

                    //委外 1未完成
                    MODEL.Monitor.MonitorInfo weiwaiM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string weiwaiSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    weiwaiSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4 INNER JOIN";
                    weiwaiSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 9)";
                    cmd.CommandText = weiwaiSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            weiwaiM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            weiwaiM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string weiwaiSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT(DISTINCT b.OrderProductId) AS CompletedTask";
                    weiwaiSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) as a INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    weiwaiSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 9) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = weiwaiSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            weiwaiM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            weiwaiM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string weiwaiSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    weiwaiSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    weiwaiSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 9)";
                    cmd.CommandText = weiwaiSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            weiwaiM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            weiwaiM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    weiwaiM.TechId = 9;
                    weiwaiM.TechName = "委外";
                    monitors.Add(weiwaiM);

                    //分堆 1未完成
                    MODEL.Monitor.MonitorInfo fenduiM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string fenduiSql1 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS NoCompleteArea,COUNT(DISTINCT b.OrderProductId) AS NoCompleteTask";
                    fenduiSql1 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId AND a.Status <> 4 INNER JOIN";
                    fenduiSql1 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 10)";
                    cmd.CommandText = fenduiSql1;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["NoCompleteArea"] != DBNull.Value)
                            fenduiM.NoCompleteArea = Convert.ToDouble(reader["NoCompleteArea"]);
                        if (reader["NoCompleteTask"] != DBNull.Value)
                            fenduiM.NoCompleteTask = Convert.ToInt32(reader["NoCompleteTask"]);
                    }
                    reader.Close();
                    //2已完成
                    string fenduiSql2 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS CompletedArea,COUNT(DISTINCT b.OrderProductId) AS CompletedTask";
                    fenduiSql2 += " FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk)  as a INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) as b ON a.OrderProductId = b.OrderProductId AND a.Status = 4 INNER JOIN";
                    fenduiSql2 += " (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 10) and CompletedTime>='" + start + "' and CompletedTime<'" + end + "'";
                    cmd.CommandText = fenduiSql2;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["CompletedArea"] != DBNull.Value)
                            fenduiM.CompletedArea = Convert.ToDouble(reader["CompletedArea"]);
                        if (reader["CompletedTask"] != DBNull.Value)
                            fenduiM.CompletedTask = Convert.ToInt32(reader["CompletedTask"]);
                    }
                    reader.Close();
                    //3 加急级别
                    string fenduiSql3 = "SELECT round(SUM((c.Height * 2.54 / 100) * (c.Width * 2.54 / 100)*b.productNum),4) AS PriorityArea,COUNT(DISTINCT b.OrderProductId) AS PriorityTask";
                    fenduiSql3 += " FROM WorkOrderFlowList as a INNER JOIN orderInfo as b ON a.OrderProductId = b.OrderProductId and b.priority>=" + priority + " AND a.Status <> 4 INNER JOIN";
                    fenduiSql3 += " photoInfo as c ON b.OrderProductId = c.OrderProductId WHERE (a.TechId = 10)";
                    cmd.CommandText = fenduiSql3;
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader["PriorityArea"] != DBNull.Value)
                            fenduiM.PriorityArea = Convert.ToDouble(reader["PriorityArea"]);
                        if (reader["PriorityTask"] != DBNull.Value)
                            fenduiM.PriorityTask = Convert.ToInt32(reader["PriorityTask"]);
                    }
                    reader.Close();
                    fenduiM.TechId = 10;
                    fenduiM.TechName = "分堆";
                    monitors.Add(fenduiM);

                    //发货
                    //1未发货    shop_bill中的未发货+deliveryinfo中的未打印发货单
                    MODEL.Monitor.MonitorInfo fahuoM = new MINERP.MODEL.Monitor.MonitorInfo();
                    string fahuoSql1 = "select count(billid) from shop_bill where NeedDeliver=1 and WriteIntoDeliveryInfo=0";
                    cmd.CommandText = fahuoSql1;
                    int noDeliverNum = Convert.ToInt32(cmd.ExecuteScalar());
                    string fahuoSql2 = "select count(billid) from deliveryInfo where status=0";         //未打印发货单表示还未发货
                    cmd.CommandText = fahuoSql2;
                    noDeliverNum += Convert.ToInt32(cmd.ExecuteScalar());
                    fahuoM.NoDeliverTask = noDeliverNum;
                    //2已发货
                    string fahuoSql3 = "select count(billid) from deliveryInfo where status!=0 and takeawaytime>='" + start + "' and takeawaytime<'" + end + "'";
                    cmd.CommandText = fahuoSql3;
                    int deliverredNum = Convert.ToInt32(cmd.ExecuteScalar());
                    fahuoM.DeliverredTask = deliverredNum;
                    fahuoM.TechId = 0;
                    fahuoM.TechName = "发货";
                    monitors.Add(fahuoM);

                    return monitors;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获得未完成产品总平米数
        /// </summary>
        /// <returns></returns>
        public double GetNoCompletedArea()
        {
            double Area =0;
            string sql = "SELECT SUM(b.Height * 2.54 / 100 * b.Width * 2.54 / 100*a.productNum) AS NoCompleteArea FROM orderInfo a INNER JOIN photoInfo b ON a.OrderProductId = b.OrderProductId WHERE (a.status <> 8)";
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        Area = Convert.ToDouble(rdr["NoCompleteArea"]);
                        Area = Math.Round(Area, 4);
                    }
                    else
                    {
                        Area = 0;
                    }
                }
            }
            return Area;
            
        }

        /// <summary>
        /// 获得未完成产品的个数
        /// </summary>
        /// <returns></returns>
        public int GetNoCompletedProductNum()
        {
            int num = 0;
            string sql = "SELECT COUNT(*) AS productNum FROM orderInfo WHERE (status =1)";
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    num = Convert.ToInt32(rdr["productNum"]);
                }
            }
            return num;
             
        }
        /// <summary>
        /// 工厂加急未完成产品数
        /// </summary>
        /// <param name="priority"></param>
        /// <returns></returns>
        public int GetNoCompletedProductNumPriority(int priority)
        {
            int num = 0;
            string sql = string.Format("SELECT COUNT(*) AS productNum FROM orderInfo WHERE (status = 1) and Priority >={0}", priority);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    num = Convert.ToInt32(rdr["productNum"]);
                }
            }
            return num;
        }
        /// <summary>
        /// 工厂加急未完成产品面积
        /// </summary>
        /// <returns></returns>
        public double GetNoCompletedAreaPriority(int priority)
        {
            double Area = 0;
            string sql = string.Format("SELECT SUM(b.Height * 2.54 / 100 * b.Width * 2.54 / 100*a.productNum) AS NoCompleteArea FROM orderInfo a INNER JOIN photoInfo b ON a.OrderProductId = b.OrderProductId WHERE (a.status =1) and a.Priority >={0}", priority);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        Area = Convert.ToDouble(rdr["NoCompleteArea"]);
                        Area = Math.Round(Area, 4);
                    }
                    else
                    {
                        Area = 0;
                    }
                }
            }
            return Area;

        }
        /// <summary>
        /// 工厂当天已完成产品数
        /// </summary>
        /// <returns></returns>
        public int GetCompletedProductNum(DateTime start,DateTime end)
        {
            int num = 0;
            string sql = string.Format("SELECT COUNT(*) AS productNum FROM (select * from orderInfo union all select * from orderinfo_bk) as a  WHERE (status = 8) AND (CompleteTime >= '{0}') AND (CompleteTime <= '{1}')", start, end);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    num = Convert.ToInt32(rdr["productNum"]);
                }
            }
            return num;

        }
        /// <summary>
        /// 工厂当天已完成产品面积
        /// </summary>
        /// <returns></returns>
        public double GetCompletedArea(DateTime start, DateTime end)
        {
            double Area = 0;
            string sql = string.Format("SELECT SUM(b.Height * 2.54 / 100 * b.Width * 2.54 / 100*a.productNum) AS CompleteArea FROM (select * from orderInfo union all select * from orderinfo_bk) a INNER JOIN (select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo union all select photoId, OrderProductId, PhotoBarCode, photoName, Height, Width, pixWidth, pixHeight, paperTypeID, paperFaceId, picPath, OldPicPath, Remark,IsModifySize, printerId, status, TotalNum, MakeUpNum, userId, status2 from photoinfo_bk) b ON a.OrderProductId = b.OrderProductId WHERE (a.status = 8) and (CompleteTime >= '{0}') AND (CompleteTime <= '{1}')", start, end);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        Area = Convert.ToDouble(rdr["CompleteArea"]);
                        Area = Math.Round(Area, 4);
                    }
                    else
                    {
                        Area = 0;
                    }
                }
            }
            return Area;

        }
        public List<UserTaskInfo> GetUserTaskInfo(int techid, DateTime start, DateTime end, int priority)
        {
            string techName=string.Empty;
            //获得节点名称
            string sqltech = string.Format("SELECT TechName FROM tb_Tech WHERE (TechId ={0})", techid);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sqltech, null))
            {
                if (rdr.Read())
                {
                    techName = rdr["TechName"].ToString();
                }
            }
            //获得该节点下所有员工
            string sqlTechUser = string.Format("SELECT a.UserName UserName FROM users a INNER JOIN User_Tech b ON a.userID = b.UserId WHERE (b.TechId ={0} )", techid);
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sqlTechUser, null))
            {
                while (rdr.Read())
                {
                    UserTaskInfo task = new UserTaskInfo();

                    task.UserName = rdr["UserName"].ToString();
                    task.TechName = techName;
                    userTaskInfoList.Add(task);
                }
 
            }
            foreach (var item in userTaskInfoList)
            {
                //获得未完成量和未完成面积
                string sql =string.Format( "SELECT SUM(b.Width * 2.54 / 100 * b.Height * 2.54 / 100) AS NoCompleteArea, COUNT(DISTINCT a.OrderProductId) AS NoCompleteTaskNum FROM WorkOrderFlowList a INNER JOIN photoInfo b ON a.OrderProductId = b.OrderProductId INNER JOIN users c ON a.UserId = c.userID WHERE (a.TechId = {0}) AND (a.Status = 1) AND (c.UserName = '{1}')",techid,item.UserName);
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
                {
                    if (rdr.Read())
                    {
                        if(!rdr.IsDBNull(0))
                        item.NoCompleteArea =Math.Round( Convert.ToDouble(rdr["NoCompleteArea"]),4);
                        if (!rdr.IsDBNull(1))
                        item.NoCompleteTaskNum = Convert.ToInt32(rdr["NoCompleteTaskNum"]);
                    }
 
                }

                //获得加急未完成量和未完成面积
                string sqlpriority = string.Format("SELECT SUM(b.Width * 2.54 / 100 * b.Height * 2.54 / 100) AS NoCompleteArea, COUNT(DISTINCT a.OrderProductId) AS NoCompleteTaskNum FROM WorkOrderFlowList a INNER JOIN photoInfo b ON a.OrderProductId = b.OrderProductId INNER JOIN users c ON a.UserId = c.userID INNER JOIN orderInfo d ON a.OrderProductId = d.OrderProductId WHERE (a.TechId = {0}) AND (a.Status = 1) AND (c.UserName = '{1}') AND (d.Priority >= {2})", techid, item.UserName, priority);
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sql, null))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            item.PriorityArea = Math.Round(Convert.ToDouble(rdr["NoCompleteArea"]), 4);
                        if (!rdr.IsDBNull(1))
                            item.PriorityTask = Convert.ToInt32(rdr["NoCompleteTaskNum"]);
                    }

                }

                //获得当前时间段已完成量
                string sqlcompleted = string.Format("SELECT SUM(b.Width * 2.54 / 100 * b.Height * 2.54 / 100) AS CompleteArea,COUNT(DISTINCT a.OrderProductId) AS CompleteTaskNum FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) a INNER JOIN (select * from photoinfo union all select * from photoinfo_bk) b ON a.OrderProductId = b.OrderProductId INNER JOIN users c ON a.UserId = c.userID INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) d ON a.OrderProductId = d.OrderProductId WHERE (a.TechId = {0}) AND (a.Status = 4) AND (c.UserName = '{1}') AND (a.CompletedTime >= '{2}') AND (a.CompletedTime < '{3}')", techid, item.UserName, start, end);
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sqlcompleted, null))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            item.CompletedArea = Math.Round(Convert.ToDouble(rdr["CompleteArea"]), 4);
                        if (!rdr.IsDBNull(1))
                            item.CompletedTask = Convert.ToInt32(rdr["CompleteTaskNum"]);
                    }

                }

                //获得当前时间段加急已完成量
                string sqlcompletedpriority = string.Format("SELECT SUM(b.Width * 2.54 / 100 * b.Height * 2.54 / 100) AS CompleteArea,COUNT(DISTINCT a.OrderProductId) AS CompleteTaskNum FROM (select * from WorkOrderFlowList union all select * from WorkOrderFlowList_bk) a INNER JOIN (select * from photoinfo union all select * from photoinfo_bk) b ON a.OrderProductId = b.OrderProductId INNER JOIN users c ON a.UserId = c.userID INNER JOIN (select * from orderinfo union all select * from orderinfo_bk) d ON a.OrderProductId = d.OrderProductId WHERE (a.TechId = {0}) AND (a.Status = 4) AND (c.UserName = '{1}') AND (a.CompletedTime >= '{2}') AND (a.CompletedTime < '{3}') and (d.Priority >= {4})", techid, item.UserName, start, end, priority);
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conStr, CommandType.Text, sqlcompletedpriority, null))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            item.CompletedAreaPriority = Math.Round(Convert.ToDouble(rdr["CompleteArea"]), 4);
                        if (!rdr.IsDBNull(1))
                            item.CompletedTaskPriority = Convert.ToInt32(rdr["CompleteTaskNum"]);
                    }

                }
            }
            return userTaskInfoList;
        }
    }
}
